<?php
class Vistec_Lightspeed_Model_Lightspeed extends Mage_Core_Model_Abstract
{
 
    protected function _construct()
    {
        $this->_init('lightspeed/lightspeed');
    }
    
    protected function _getLsConnectorInstance()
    {        
        $dbAdapterClassName = (string) Mage::getConfig()->getNode()->global->resource->connection->types->{"pdo_mysql"}->class;
    	if ($dbAdapterClassName)
        {
            return new $dbAdapterClassName();
        }
        
        return null;
    }
    
    protected function _getLsConnectionDetails()
    {
    	return array(
            'dbname'        => Mage::getStoreConfig('lightspeed/backend/lightspeed_database_name'),
            'username'      => Mage::getStoreConfig('lightspeed/backend/lightspeed_username'),
            'password'      => Mage::getStoreConfig('lightspeed/backend/lightspeed_password'),
            'host'          => Mage::getStoreConfig('lightspeed/backend/lightspeed_server_address'),
            'port'          => Mage::getStoreConfig('lightspeed/backend/lightspeed_port'),
        );
    }
    
    protected function _lsQuery($sql)
    {
    	$vistec_connect = $this->_getLsConnectorInstance();
        $connectionProperties = $this->_getLsConnectionDetails();
        return $vistec_connect->getConnection($connectionProperties)->fetchAll($sql);
    }    
    
	public function getIds()
    {        
		
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_web_enabled_only'))	{
    		$sql="SELECT rowid FROM xlsws_product WHERE web = 1";	
    	} else {
    		$sql="SELECT rowid FROM xlsws_product";	
    	}
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter'))	{
    		$sql.=" AND code ".Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter');
    	}
    	   	
		$results = $this->_lsQuery($sql);
		$arrayIds=array();
		foreach($results as $row)
		{
			$arrayIds[]=$row['rowid'];
		}
		return $arrayIds;
    }
    
    public function getImportIds()
    {
		$lastImport = Mage::getStoreConfig('lightspeed/backend/last_import');
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_web_enabled_only'))	{
    		$sql="SELECT rowid FROM xlsws_product WHERE web = 1 AND (created >= '{$lastImport}' OR modified >= '{$lastImport}')";	
    	} else {
    		$sql="SELECT rowid FROM xlsws_product WHERE created >= '{$lastImport}'";	
    	}
		
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter'))	{
    		$sql.=" AND code ".Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter');
    	}
		$results = $this->_lsQuery($sql);
		$arrayIds=array();
		foreach($results as $row)
		{
			$arrayIds[]=$row['rowid'];
		}		
		return $arrayIds;
    }
    
	public function getUpdateIds()
    {
		$lastImport = Mage::getStoreConfig('lightspeed/backend/last_update');  

   		if(Mage::getStoreConfig('lightspeed/general/lightspeed_web_enabled_only'))	{
    		$sql="SELECT rowid FROM xlsws_product WHERE web = 1 AND modified >= '{$lastImport}'";	
    	} else {
    		$sql="SELECT rowid FROM xlsws_product WHERE modified >= '{$lastImport}'";	
    	}
		
		$sql="SELECT rowid FROM xlsws_product WHERE modified >= '{$lastImport}' ";	
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter'))	{
    		$sql.=" AND code ".Mage::getStoreConfig('lightspeed/general/lightspeed_import_filter');
    	}
		$results = $this->_lsQuery($sql);
		$arrayIds=array();
		foreach($results as $row)
		{
			$arrayIds[]=$row['rowid'];
		}
		//Also get ids from updated images 
		$sql = "SELECT rowid FROM xlsws_images WHERE modified >= '{$lastImport}'";
		$results = $this->_lsQuery($sql);
		$imageIds=array();
		foreach($results as $row)	{
			
			$imageIds[]=$row['rowid'];
			
		}
		if(count($imageIds)>0)
		{
			$strImageIds=implode(",",$imageIds);
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_web_enabled_only'))	{
	    		$sql="SELECT rowid FROM xlsws_product WHERE web = 1 AND image_id IN({$strImageIds})";
	    	} else {
	    		$sql="SELECT rowid FROM xlsws_product WHERE image_id IN({$strImageIds})";
	    	}
			$results = $this->_lsQuery($sql);
	    	foreach($results as $row)
			{
				if(!in_array($row['rowid'],$arrayIds))
				{
				$arrayIds[]=$row['rowid'];
				}
			}
	    	$sql = "SELECT product_id as rowid FROM xlsws_product_image_assn WHERE image_id IN({$strImageIds})";
			$results = $this->_lsQuery($sql);
			$enabledIds = $this->getIds();
	    	foreach($results as $row)
			{
				if((!in_array($row['rowid'],$arrayIds)) AND (in_array($row['rowid'],$enabledIds)))
				{
				$arrayIds[]=$row['rowid'];
				}
			}
		}
		
		return $arrayIds;
    }
    
    public function getProducts($id)
    {
    	if(is_array($id))  	{
    		$id=implode(",",$id);
    	}
    	$sql="SELECT * FROM xlsws_product WHERE rowid IN ({$id})";
    	
    	$result=$this->_lsQuery($sql);
    	return $result;
    }
    
    public function saveProducts($lsProductArray)
    {
    	$resultsArray=array();
    	foreach($lsProductArray as $lsProduct)
    	{
    		$resultsArray[]=$this->saveProduct($lsProduct);
    	}
    	return $resultsArray;
    }
    
    public function saveProduct($lsProduct)
    {
    	if($lsProduct['master_model']==1)   	{
    		return $this->saveConfigurableProduct($lsProduct);
    	}
    	else {
    		return $this->saveSimpleProduct($lsProduct);
    	}
    }
    
	public function updateProducts($lsProductArray)    {
    	$resultsArray=array();
    	foreach($lsProductArray as $lsProduct)
    	{
    		$resultsArray[]=$this->updateProduct($lsProduct);
    	}
    	return $resultsArray;
    }
    
    public function updateProduct($lsProduct)    {
    	if($lsProduct['master_model']==1)   	{
    		return $this->updateConfigurableProduct($lsProduct);
    	}
    	else {
    		return $this->updateSimpleProduct($lsProduct);
    	}
    }
    
    
    
    public function saveConfigurableProduct($lsProduct)	{
    	//save
    	
    	
    	//result
    	$message = array(
    		"Type"=>"Simple",
    		"Result"=>"Success",
    	);
    	return $message;
    }
    
    public function saveSimpleProduct($lsProduct)	{
    	
    	//save
    	$product = Mage::getModel('catalog/product');	
		$productId = $product->getIdBySku($lsProduct['code']);
		//If the product doesn't already exist...
		$resultMessage = "Success";
		if (!$productId) 		{	 	
					
			//$product = Mage::getModel('catalog/product')->loadByAttribute('sku',$product['code']);
			$mageProduct = Mage::getModel('catalog/product');							
			$mageProduct->setTypeId("simple");
			//Set Store ID to 1 - Not Sure Why - This is the default store view, but it doesn't hide it from the other store views
			$mageProduct->setStoreId(1);
			//Set Attribute Set based on Config
			$mageProduct->setAttributeSetId(Mage::getStoreConfig('lightspeed/general/default_attribute_set'));
			//Set Websites based on Config
			$websitesArray=explode(",",Mage::getStoreConfig('lightspeed/general/lightspeed_import_website'));
			$mageProduct->setWebsiteIds($websitesArray);
			$mageProduct->setSku($lsProduct['code']);
			
			//Set Price Based on Config
			$priceField=Mage::getStoreConfig('lightspeed/pricing/lightspeed_pricing_source');
			if($priceField=="sell_tax_inclusive")	{
				
				$price = $lsProduct["sell_tax_inclusive"];
				
				/**
				$taxRate = 100 + (double) Mage::getStoreConfig('lightspeed/pricing/default_tax_rate');
				$taxRate=$taxRate/100;
				//deduct tax
				$priceField=Mage::getStoreConfig('lightspeed/pricing/lightspeed_pricing_source');
				$price = round($lsProduct["sell_tax_inclusive"]/$taxRate,2);
				**/	
				$mageProduct->setPrice($price);
			}
			else {
				$mageProduct->setPrice($lsProduct[$priceField]);
			}		
			
			
			
			//name, description etc
			$mageProduct->setName($lsProduct['name']);
			if($lsProduct['description']!='') $mageProduct->setDescription($lsProduct['description']); else $mageProduct->setDescription($lsProduct['name']);
			if($lsProduct['description_short']!='') $mageProduct->setShortDescription($lsProduct['description_short']);else $mageProduct->setShortDescription($lsProduct['name']);
			
			//visibility
			$mageProduct->setVisibility(Mage::getStoreConfig('lightspeed/general/lightspeed_visibility'));
			
			//tax class
			$mageProduct->setTaxClassId(Mage::getStoreConfig('lightspeed/pricing/default_tax_class'));
			
			//weight
			if($lsProduct['product_weight']==0) {
				$lsProduct['product_weight']=Mage::getStoreConfig('lightspeed/general/default_product_weight');
			}			
			$mageProduct->setWeight($lsProduct['product_weight']);

			//created time
			$mageProduct->setCreatedAt(strtotime('now'));	
			
			//$mageProduct->setLightspeedId($lsProduct['rowid']);
			
			
			
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_import_images'))	{
				$image=false;
				if(!is_null($lsProduct['image_id'])) $image=$this->getImages($lsProduct['rowid'],$lsProduct['code'],$lsProduct['image_id']);
				if($image)			{
					$mageProduct->setMediaGallery (array('images'=>array (), 'values'=>array ()));
					//echo "image count = ".count($image);
					for($k=0;$k<count($image);$k++)
					{
						try {
							if($image[$k]["first"]) {
								$galleryArray=array ('image','small_image','thumbnail');
							}
							else	{
								$galleryArray=false;								
							}
							//echo Mage::getBaseDir('media') . DS . 'import' . DS . $image[$k];
							$mageProduct->addImageToMediaGallery (Mage::getBaseDir('media') . DS . 'import' . DS . $image[$k]["file"], $galleryArray, true, false);
			            }
						catch (Exception $e) {};
						}
				}
			}
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_product_status')==99)	{
				if(($lsProduct['sell']!=0)) $mageProduct->setStatus(1); else $mageProduct->setStatus(2);
			}
			else
			{
				$mageProduct->setStatus(Mage::getStoreConfig('lightspeed/general/lightspeed_product_status'));
			}

			//Get Category Config
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_category_handling')=="master")	{
					//Import to Default Category
					$strCats=Mage::getStoreConfig('lightspeed/general/lightspeed_default_category');								
					$categoryIds = $this->_addCategories($strCats, $store);
					if ($categoryIds) {
				               $mageProduct->setCategoryIds($categoryIds);
					}
				}
				else	{
					
					//Get Category from Lightspeed Database
					$sql="SELECT category_id FROM xlsws_product_category_assn WHERE product_id = {$lsProduct['rowid']}";
					$categoryResults = $this->_lsQuery($sql);
					$arrayCats=array();
					foreach($categoryResults as $category)	{
						$arrayCats[] = $this->_lightspeed_Categories($category['category_id']);
					}
					$strCats=implode(",",$arrayCats);
					$store = Mage::app()->getStore(Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID);
					if (!empty($arrayCats) && trim($strCats)!="") {
						$categoryIds = $this->_addCategories($strCats, $store);
						if ($categoryIds) {
					               $mageProduct->setCategoryIds($categoryIds);
					   }
				    }
				    else	{
				    	$categoryIds = $this->_addCategories(Mage::getStoreConfig('lightspeed/general/lightspeed_default_category'), $store);
						if ($categoryIds) {
					               $mageProduct->setCategoryIds($categoryIds);
					   }
				    }
									
			}
			
				
					
			$mageProduct->setIsMassupdate(true);
	        $mageProduct->setExcludeUrlRewrite(true);
			$mageProduct->save();
													 
			/* ADDITIONAL OPTIONS 
					 
			$mageProduct->setCost();
			$mageProduct->setInDepth();
			$mageProduct->setKeywords();
					 
			*/
					 				 
			// "Stock Item" still required regardless of whether inventory
			// control is used, or stock item error given at checkout!
					 
			$stockItem = Mage::getModel('cataloginventory/stock_item');
			$stockItem->assignProduct($mageProduct);
			//$stockItem->loadByProduct($mageProduct->getId());
			
			$stockItem->setData('stock_id', 1);
			$stockItem->setData('store_id', 1);
			
			if(Mage::getStoreConfig('lightspeed/inventory/lightspeed_inventory_behaviour'))		{
				$stockItem->setData('manage_stock', $lsProduct['inventoried']);
				if($lsProduct['inventory']>0 or $lsProduct['inventoried']==1) {
					$stockItem->setData('is_in_stock', 1); 
									}
				else	{
					$stockItem->setData('is_in_stock', 0);
				}
			}
			else {
				$stockItem->setData('manage_stock', 0);
				$stockItem->setData('is_in_stock', 1);
			}					
			$stockItem->setData('use_config_manage_stock', 0);
			$stockItem->setData('min_sale_qty', 0);
			$stockItem->setData('use_config_min_sale_qty', 1);
			$stockItem->setData('max_sale_qty', 10);
			$stockItem->setData('use_config_max_sale_qty', 1);
			$stockItem->setQty($lsProduct['inventory']);
			$stockItem->save();
			$resultMessage = "Product SKU {$lsProduct['code']} Imported;";		
			}
			else			{
			$resultMessage = "Product SKU {$lsProduct['code']} Already Exists in Magento: Not Imported";
			}
    	
    	//result
    	$message = array(
    		"Type"=>"Simple",
    		"Result"=>$resultMessage,
    	);
    	return $resultMessage;
    }
    
    public function updateConfigurableProduct($lsProduct)	{
    	//save
    	
    	
    	//result
    	$message = array(
    		"Type"=>"Simple",
    		"Result"=>"Success",
    	);
    }
    
    
	public function updateSimpleProduct($lsProduct)	{
		
			$product = Mage::getModel('catalog/product');	
			$productId = $product->getIdBySku($lsProduct['code']);
			if ($productId) 
			{	 	
			
				$mageProduct = Mage::getModel('catalog/product')->loadByAttribute('sku',$lsProduct['code']);				
				//Set Price Based on Config
			$priceField=Mage::getStoreConfig('lightspeed/pricing/lightspeed_pricing_source');
			if($priceField=="sell_tax_inclusive")	{
				
				/*
				$taxRate = 100 + (double) Mage::getStoreConfig('lightspeed/pricing/default_tax_rate');
				$taxRate=$taxRate/100;
				//deduct tax
				$priceField=Mage::getStoreConfig('lightspeed/pricing/lightspeed_pricing_source');
				$price = round($lsProduct["sell_tax_inclusive"]/$taxRate,2);
				*/
				$price = $lsProduct["sell_tax_inclusive"];
				$mageProduct->setPrice($price);
				
			}
			else {
				$mageProduct->setPrice($lsProduct[$priceField]);
			}		
			
			
			
			//name, description etc
			$mageProduct->setName($lsProduct['name']);
			if($lsProduct['description']!='') $mageProduct->setDescription($lsProduct['description']); else $mageProduct->setDescription($lsProduct['name']);
			if($lsProduct['description_short']!='') $mageProduct->setShortDescription($lsProduct['description_short']);else $mageProduct->setShortDescription($lsProduct['name']);
			
			//visibility
			$mageProduct->setVisibility(Mage::getStoreConfig('lightspeed/general/lightspeed_visibility'));
			
			//tax class
			$mageProduct->setTaxClassId(Mage::getStoreConfig('lightspeed/pricing/default_tax_class'));
			
			//weight
			if($lsProduct['product_weight']==0) {
				$lsProduct['product_weight']=Mage::getStoreConfig('lightspeed/general/default_product_weight');
			}			
			$mageProduct->setWeight($lsProduct['product_weight']);

			//created time
			$mageProduct->setCreatedAt(strtotime('now'));	
			
			//$mageProduct->setLightspeedId($lsProduct['rowid']);
			
			
			
			
			  
			 if(Mage::getStoreConfig('lightspeed/general/lightspeed_import_images'))	{
				$image=false;
				if(!is_null($lsProduct['image_id'])) $image=$this->getImages($lsProduct['rowid'],$lsProduct['code'],$lsProduct['image_id']);
				
				if($image)			{
					$mageProduct->setMediaGallery (array('images'=>array (), 'values'=>array ()));
					//echo "image count = ".count($image);
					for($k=0;$k<count($image);$k++)
					{
						try {
							if($image[$k]["first"]) {
								$galleryArray=array ('image','small_image','thumbnail');
							}
							else	{
								$galleryArray=false;								
							}
							//echo Mage::getBaseDir('media') . DS . 'import' . DS . $image[$k];
							$mageProduct->addImageToMediaGallery (Mage::getBaseDir('media') . DS . 'import' . DS . $image[$k]["file"], $galleryArray, true, false);
			            }
						catch (Exception $e) {};
						}
				}
				
			}
			
			
			
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_product_status')==99)	{
				if(($lsProduct['sell']!=0)) $mageProduct->setStatus(1); else $mageProduct->setStatus(2);
			}
			else
			{
				$mageProduct->setStatus(Mage::getStoreConfig('lightspeed/general/lightspeed_product_status'));
			}

			//Get Category Config
			if(Mage::getStoreConfig('lightspeed/general/lightspeed_category_handling')=="master")	{
					//Import to Default Category
					$strCats=Mage::getStoreConfig('lightspeed/general/lightspeed_default_category');								
					$categoryIds = $this->_addCategories($strCats, $store);
					if ($categoryIds) {
				               $mageProduct->setCategoryIds($categoryIds);
					}
				}
				else	{
					
					//Get Category from Lightspeed Database
					$sql="SELECT category_id FROM xlsws_product_category_assn WHERE product_id = {$lsProduct['rowid']}";
					$categoryResults = $this->_lsQuery($sql);
					$arrayCats=array();
					foreach($categoryResults as $category)	{
						$arrayCats[] = $this->_lightspeed_Categories($category['category_id']);
					}
					$strCats=implode(",",$arrayCats);
					$store = Mage::app()->getStore(Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID);
					if (!empty($arrayCats) && trim($strCats)!="") {
						$categoryIds = $this->_addCategories($strCats, $store);
						if ($categoryIds) {
					               $mageProduct->setCategoryIds($categoryIds);
					   }
				    }
				    else	{
				    	$categoryIds = $this->_addCategories(Mage::getStoreConfig('lightspeed/general/lightspeed_default_category'), $store);
						if ($categoryIds) {
					               $mageProduct->setCategoryIds($categoryIds);
					   }
				    }
									
			}
			
					
			$mageProduct->setIsMassupdate(true);
	        $mageProduct->setExcludeUrlRewrite(true);
			$mageProduct->save();
				 
				/* ADDITIONAL OPTIONS 
				 
				   $mageProduct->setCost();
				   $mageProduct->setInDepth();
				   $mageProduct->setKeywords();
				 
				*/
				 				 
				// "Stock Item" still required regardless of whether inventory
				// control is used, or stock item error given at checkout!
				 
				$stockItem = Mage::getModel('cataloginventory/stock_item');
				$stockItem->loadByProduct($mageProduct->getId());
				if(Mage::getStoreConfig('lightspeed/inventory/lightspeed_inventory_behaviour'))		{
					$stockItem->setData('manage_stock', $lsProduct['inventoried']);
					if($lsProduct['inventory']>0 or $lsProduct['inventoried']==1) {
						$stockItem->setData('is_in_stock', 1); 
										}
					else	{
						$stockItem->setData('is_in_stock', 0);
					}
				}
				else {
					$stockItem->setData('manage_stock', 0);
					$stockItem->setData('is_in_stock', 1);
				}					
				$stockItem->setData('use_config_manage_stock', 0);
				$stockItem->setData('min_sale_qty', 0);
				$stockItem->setData('use_config_min_sale_qty', 1);
				$stockItem->setData('max_sale_qty', 10);
				$stockItem->setData('use_config_max_sale_qty', 1);
				$stockItem->setQty($lsProduct['inventory']);
				$stockItem->save();
				$resultMessage = "Product SKU {$lsProduct['code']} Updated;";		
				}
				else			{
				$resultMessage = "<p>Product SKU {$lsProduct['code']} Not Found In Magento, Please Run Import Again to Upload This Product</p>";
			}				
		return $resultMessage;	
	}
    
	private function _lightspeed_categories($id)
	{
		$test=true;
		$array=array();
		$string="";
		while($test)
		{
			$sql="SELECT rowid,name,parent FROM xlsws_category WHERE rowid={$id}";
			$results = $this->_lsQuery($sql);
			if(isset($results[0]))
			{
			$category=$results[0];
			array_unshift($array,$category['name']);
			if($category['parent']!='0')	{
				$id=$category['parent'];
			} else	{
				$test=false;
			}
			$string=implode("/",$array);
			}
			else $test=false;
		}
		return $string;
	}
	
	protected $_categoryCache = array();
	protected function _addCategories($categories, $store)
    {
        $rootId = 2;
        
        $rootPath = '1/'.$rootId;
        
        if (empty($this->_categoryCache[$store->getId()])) {
            $collection = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToSelect('name');
            $collection->getSelect()->where("path like '".$rootPath."/%'");

            foreach ($collection as $cat) {
                $pathArr = explode('/', $cat->getPath());
                $namePath = '';
                for ($i=2, $l=sizeof($pathArr); $i<$l; $i++) {
                	if(trim($pathArr[$i])!="")
                	{
                    $name = $collection->getItemById($pathArr[$i])->getName();
                    $namePath .= (empty($namePath) ? '' : '/').trim($name);
                	}
                }
                $cat->setNamePath($namePath);
            }
            
            $cache = array();
            foreach ($collection as $cat) {
                $cache[strtolower($cat->getNamePath())] = $cat;
                $cat->unsNamePath();
            }
            $this->_categoryCache[$store->getId()] = $cache;
        }
        $cache =& $this->_categoryCache[$store->getId()];
        
        $catIds = array();
        foreach (explode(',', $categories) as $categoryPathStr) {
            $categoryPathStr = preg_replace('#\s*/\s*#', '/', trim($categoryPathStr));
            if (!empty($cache[$categoryPathStr])) {
                $catIds[] = $cache[$categoryPathStr]->getId();
                echo "From Cache {$categoryPathStr}: ".$cache[$categoryPathStr]->getId();
                print_r($catIds);
                continue;
            }
            $path = $rootPath;
            $namePath = '';
            foreach (explode('/', $categoryPathStr) as $catName) {
            	$catId=false;
                $namePath .= (empty($namePath) ? '' : '/').strtolower($catName);
                if (empty($cache[$namePath])) {
                	
                    $cat = Mage::getModel('catalog/category')
                        ->setStoreId($store->getId())
                        ->setPath($path)
                        ->setName($catName)
                        ->setIsActive(1)
                        ->save(); 
                    $cache[$namePath] = $cat;
                }
                $catId = $cache[$namePath]->getId();
                $catIds[] = $catId;
                   
                $path .= '/'.$catId;
            }
            
        }
        $catIds[]=2;
        return join(',', $catIds);
    }
    
    public function cleanupCategories()   {
    	
		
    	//Mage::app()->getWebsite($websiteId)->getStoreIds();
    	
    	$store = Mage::app()->getStore(Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID);
    	//importing into default Category Only at the moment
    	$rootId = 2;        
        $rootPath = '1/'.$rootId;
        
    	
    	//Get Root Categories From LS		
		$sql="SELECT rowid,name,parent FROM xlsws_category WHERE parent=0 AND child_count != 0";
		$primaryCategories = $this->_lsQuery($sql);
		
		
    	//Loop Through For Each Category
    	foreach($primaryCategories as $primaryCategory)
    	{ 
	    	//Check if it already exists
	    		    	 
	    	 $category = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToFilter('level',2)
                ->addAttributeToSort('id', 'ASC')
                ->addAttributeToFilter('name',$primaryCategory['name']);
                
	    	 //the above should only return one result!
	    	 
             $countResult=$category->count();
 	         
             //echo "Looking for ".$primaryCategory['name']." found {$countResult}<br />";
             
	    	 $i=0;
             foreach ($category as $cat)
	    	 {
	    	 	//If this is not the first one then more than one exists...KILL IT!!!
	    	 	if($i)
	    	 	{
	    	 		Mage::Log("There is more than one Primary Category Called  ".$primaryCategory['name']." // dups not allowed, deleting last entered<br />");
	    	 		try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
	    	 	}
	    	 	else
	    	 	{
	    	 		$category = $cat;
	    	 	}
	    	 	$i++;
	    	 }
                
	    	 
	    	 if(!$category || !$countResult)	{
	    	 	$category = Mage::getModel('catalog/category');
	    	 //If Not Create It	
	    	 	$category->setStoreId($store->getId())
	    	 		->setPath($rootPath)
	    	 		->setName($primaryCategory['name'])
	    	 		->setIsActive(1)
	    	 		->save();
	       	 	//echo "Created: ".$primaryCategory['name']."<br />";
	       	 	Mage::Log("Created: ".$primaryCategory['name']."<br />");
	    	 	
	    	 	
	    	 }
	    	 $primaryCategoryId=$category->getId();
	    	 //Get All Children Categories
	    	 $sql="SELECT rowid,name,parent FROM xlsws_category WHERE parent={$primaryCategory['rowid']}";
			 $secondaryCategories = $this->_lsQuery($sql);
			 
	    	  $notIn2=array();  	
	    	//Loop Through For Each Category 
	    	foreach($secondaryCategories as $secondaryCategory)	{
	    	
		    	$notIn2[]=$secondaryCategory['name'];
	    		
		    	$category2 = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToFilter('level',3)
                ->addAttributeToFilter('parent_id',$primaryCategoryId)
                ->addAttributeToSort('id', 'ASC')
                ->addAttributeToFilter('name',$secondaryCategory['name']);
                
                $countResult=$category2->count();
                //echo "Looking for ".$secondaryCategory['name']." in ".$primaryCategory['name']." found {$countResult}<br />";
                
	    	$i=0;
             foreach ($category2 as $cat)
	    	 {
	    	 	//If this is not the first one then more than one exists...KILL IT!!!
	    	 	if($i)
	    	 	{
	    	 		Mage::Log("There is more than one ".$secondaryCategory['name']." in this Primary Category {$primaryCategory['name']} // dups not allowed, deleting last entered<br />");
	    	 		try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
	    	 	}
	    	 	else
	    	 	{
	    	 		$category2 = $cat;
	    	 	}
	    	 	$i++;
	    	 }
		    	
		    	 if(!$category2 || !$countResult)	{
		    	 //If Not Create It	
		    	 	$category2 = Mage::getModel('catalog/category');
		    	 	$category2->setStoreId($store->getId())
		    	 		->setPath($rootPath.'/'.$primaryCategoryId)
		    	 		->setName($secondaryCategory['name'])
		    	 		->setIsActive(1)
		    	 		->save();
		    	 	//echo "Created: ".$secondaryCategory['name']." in ".$primaryCategory['name']."<br />";
		    	 	Mage::Log("Created: ".$secondaryCategory['name']." in ".$primaryCategory['name']."<br />");
		    	 	
		    	 }
		    	 //print_r($category2);
				$secondaryCategoryId=$category2->getId();
				
				
				
		    	$sql="SELECT rowid,name,parent FROM xlsws_category WHERE parent={$secondaryCategory['rowid']}";
			 	$tertiaryCategories = $this->_lsQuery($sql);
			 	$notIn3=array();
		    	foreach($tertiaryCategories as $tertiaryCategory)	{
		    	
		    		$notIn3[]=$tertiaryCategory['name'];
			    	
			    	$category3 = Mage::getModel('catalog/category')->getCollection()
               		->setStore($store)
                	->addAttributeToFilter('level',4)
                	->addAttributeToFilter('parent_id',$secondaryCategoryId)
                	->addAttributeToSort('id', 'ASC')
                	->addAttributeToFilter('name',$tertiaryCategory['name']);
                	
                	
                	$countResult=$category3->count();
                	//echo "Looking for ".$tertiaryCategory['name']." in ".$primaryCategory['name']."/".$secondaryCategory['name']." found {$countResult}<br />";
                	
		    	$i=0;
             foreach ($category3 as $cat)
	    	 {
	    	 	//If this is not the first one then more than one exists...KILL IT!!!
	    	 	if($i)
	    	 	{
	    	 		Mage::Log("There is more than one ".$tertiaryCategory['name']." in Secondary Category {$primaryCategory['name']}/{$secondaryCategory['name']} // dups not allowed, deleting last entered<br />");
	    	 		try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
	    	 	}
	    	 	else
	    	 	{
	    	 		$category3 = $cat;
	    	 	}
	    	 	$i++;
	    	 }
			    	
			    	 if(!$category3 || !$countResult)	{
			    	 //If Not Create It	
			    	    $category3 = Mage::getModel('catalog/category');
			    	 	$category3->setStoreId($store->getId())
			    	 		->setPath($rootPath.'/'.$primaryCategoryId.'/'.$secondaryCategoryId)
			    	 		->setName($tertiaryCategory['name'])
			    	 		->setIsActive(1)
			    	 		->save();
			    	 	Mage::Log("Created: ".$tertiaryCategory['name']." in ".$primaryCategory['name']."/".$secondaryCategory['name']."<br />");
			    	 }
			    	 //$category3->delete();
			    	 //unset($category3);
		    	}
		    	//Now Delete All Tertiary Categories Not Found
	    		$collection = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToFilter('level',4)
                ->addAttributeToFilter('parent_id',$secondaryCategoryId)
                ->addAttributeToFilter('name',array('nin'=>$notIn3));

            	foreach ($collection as $cat) { 
            		Mage::Log("Tertiary Category ".$cat->getName()."Not Found {$primaryCategory['name']}/{$secondaryCategory['name']}, Deleting<br />");
					try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
            	}
		    	
	    	}
    			//Now Delete All Tertiary Categories Not Found
	    		$collection = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToFilter('level',3)
                ->addAttributeToFilter('parent_id',$primaryCategoryId)
                ->addAttributeToFilter('name',array('nin'=>$notIn2));

            	foreach ($collection as $cat) { 
            		Mage::Log("Secondary Category ".$cat->getName()."Not Found in {$primaryCategory['name']}, Deleting<br />");
					try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
            	}
    	}
    	
    	//NOw Delete All Primary Categories
		
		$primaryCategoriesArray=array();
		foreach ($primaryCategories as $cat) {
			$primaryCategoriesArray[]=$cat['name'];
			$primaryCategoriesArray[]=Mage::getStoreConfig('lightspeed/general/lightspeed_default_category');
			$primaryCategoriesArray[]=Mage::getStoreConfig('lightspeed/general/lightspeed_featured_category');
		}
		
    	$collection = Mage::getModel('catalog/category')->getCollection()
                ->setStore($store)
                ->addAttributeToFilter('level',2)
                ->addAttributeToFilter('name',array('nin'=>$primaryCategoriesArray));

            foreach ($collection as $cat) { 
            		Mage::Log("Primary Category ".$cat->getName()."Not Found, Deleting<br />");
					try	{
						@$cat->delete();
					}
						catch (Exception $e)	{
					
					}    	
            }
            
    	
    	
    	return true;
    	
    }
    
    public function cleanupProducts()
    {
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_product_handling')=="import")
    	{
    		Mage::log("Product Handling Set to Import - No Products Deleted");
    		return true;
    	}
    	Mage::log("Product Handling Not Set to Import - Deleting Products");
    	$counter=0;
    	$not_in=array();
    	$sql="SELECT code FROM xlsws_product";
    	if(Mage::getStoreConfig('lightspeed/general/lightspeed_web_enabled_only'))	{
    		$sql="SELECT code FROM xlsws_product WHERE web = 1";	
    	} else {
    		$sql="SELECT code FROM xlsws_product";	
    	}
		$lsProducts = $this->_lsQuery($sql);
		foreach($lsProducts as $lsProduct)	{
			
			$not_in[]=$lsProduct['code'];
			
		}
		$countP = count($not_in);
		Mage::log("Found {$countP} Products in LightSpeed");
    	
	    //
	    $products   = Mage::getModel('catalog/product')->setStoreId(Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID)->getCollection()
            ->addFieldToFilter('sku', array('nin' => $not_in))
            ->addAttributeToSelect('sku');
            	    
	   $pCounter=0;
	      foreach ($products as $product)
	      {
	      	$pCounter++;
	        try {
		         
		         if( Mage::getStoreConfig('lightspeed/general/lightspeed_product_handling')=="slave")        {
		         	Mage::dispatchEvent('catalog_controller_product_delete', array('product' => $product));
		          	$product->delete();
		          	$counter++;  
		         } else	{
		         	//handling is gentle;
		         	$product->setStatus(0);
		         	$product->save();
		         	$counter++;  
		         }
		             
	        } catch (Exception $e) {
	          echo "<br/>Can't delete/disable product: $e";
	        }
	        
	      }
	      Mage::log("Found {$pCounter} Products in Magento That Are Not In LightSpeed, Delete / Disabled {$counter}");
	      return $counter; 
    }
    
	private function getImages($id,$sku,$first_image_id)		{
			$sql = "SELECT image_id FROM xlsws_product_image_assn WHERE product_id = {$id}";	
			$images = $this->_lsQuery($sql);
			$results=array();
			$results[]=$first_image_id;
			foreach($images as $image)	{
				
				$results[]=$image['image_id'];
				
			}
			
			
			$mageProduct = Mage::getModel('catalog/product')->loadByAttribute('sku',$sku);
			if($mageProduct)	{
				$images = Mage::getModel('catalog/product')->load($mageProduct->getId())->getMediaGalleryImages();
				$imageHashArray = array();
				
					foreach	($images as $image) {
						$imageHashArray[]=md5_file($image['path']);
					}
				
			
				$output=array();
				$j=0;
				$k=0;
				foreach($results as $result)
				{
					$imageId = $result;
					$sql="SELECT * from xlsws_images WHERE rowid = {$imageId}";
					$images = $this->_lsQuery($sql);
					$image=$images[0];
					if($image['image_data']!="")
					{
						$filename=strtolower($sku);
						$filename = preg_replace("/[^a-zA-Z0-9]/", "", $filename);
						$location="media/import/";
						if($j==0) $filename.=".jpg";
						else $filename.="_{$j}.jpg";
						$justFilename=$filename;
						$filename=$location."/".$filename;				
						$file=fopen($filename,'w');
						if (fwrite($file,$image['image_data'])) {
							fclose($file);
							$imageHash=md5_file($filename);
							if(!in_array($imageHash,$imageHashArray))		{
								$outputHolder=array("file"=>$justFilename,"id"=>$imageId);
								if($k==0)	{
									$outputHolder['first']=true;
								}
								else	{
									$outputHolder['first']=false;
								}
								$output[]=$outputHolder;
								$j++;
								$k++;
							}
							else	{
								unlink($filename);
								$k++;
							}
										
						
						}
					}
				}
			}
			if(empty($output)) return false;
			else return $output;			
	}
	
	public function importLocalImages()	{		
		
		$sql="SELECT code FROM xlsws_product";	    	
		$results = $this->_lsQuery($sql);
		$numRows=count($results);
		
		$product = Mage::getModel('catalog/product');
		$i=0;
		$importDir=Mage::getStoreConfig('lightspeed/general/local_image_import_source_dir');
		foreach($results as $lsProduct)		{	
			
			$importDir=str_replace("/",DS,$importDir);
			$importDir=str_replace("\\",DS,$importDir);
			
			$lsProduct['code'] = strtolower(preg_replace("/[^a-zA-Z0-9\s]/", "", $lsProduct['code']));
			
	
			//echo "Checking whether ". Mage::getBaseDir('media') . DS . 'import' . DS . $lsProduct['code'].".jpg exists";
			if(file_exists(Mage::getBaseDir() . DS . $importDir . DS . $lsProduct['code'].".jpg"))	{
				$i++;
			
			$productId = $product->getIdBySku($lsProduct['code']);
			if ($productId) 	{	 	
				
					
						$mageProduct = Mage::getModel('catalog/product')->load($productId);
						$images = $mageProduct->getMediaGalleryImages();
						$imageHashArray = array();
						foreach	($images as $image) {
							$imageHashArray[]=md5_file($image['path']);
						}
						$imageHash=md5_file(Mage::getBaseDir() . DS . $importDir . DS . $lsProduct['code'].".jpg");
						if(!in_array($imageHash,$imageHashArray))		{
						
						
						$mageProduct->setMediaGallery (array('images'=>array (), 'values'=>array ()));
						try {
								$mageProduct->addImageToMediaGallery (Mage::getBaseDir() . DS . $importDir . DS . $lsProduct['code'].".jpg", array ('image','small_image','thumbnail'), true, false);
				        }
						catch (Exception $e) {
						
							echo $e;
							
						};
								
						//$mageProduct->setIsMassupdate(true);
	        			//$mageProduct->setExcludeUrlRewrite(true);
						$mageProduct->save();
						} 
						else	{
							
							unlink(Mage::getBaseDir() . DS . $importDir . DS . $lsProduct['code'].".jpg");
							
						}
					}
					
				
					
				
					
				//print_r($product_api);
				 
			}
			else	{
				
			}
					
		}
		
		return $i;		
	}
    
}